package com.yy.system.pool;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@SuppressWarnings("unchecked")
public class DBConctionQuery {
	private Connection conn = null;
	public DBConctionQuery(Connection conn) {
		this.conn = conn;
	}
	/**
	 * 查询数据
	 * @param sql
	 * @param objs
	 * @return
	 */
	public List<Map<String, Object>> query(String sql, Object... objs) {
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			if (objs != null && objs.length > 0) {
				for (int i = 1;i <= objs.length;i ++) {
					ps.setObject(i, objs[i - 1]);
				}
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = ps.getMetaData();
			int size = rsmd.getColumnCount();
			String[] names = new String[size];
			for (int i = 1; i <= size; i++) {
				try {
					names[i - 1] = rsmd.getColumnName(i);
				} catch (Exception e) {
				}
			}
			
			Map<String, Object> map = null;
			while (rs.next()) {
				map = new HashMap<String, Object>();
				for (int i = 0; i < names.length; i++) {
					map.put(names[i].toUpperCase(), rs.getObject(names[i]));
				}
				list.add(map);
			}
		} catch (Exception e) {
			if (e.getMessage().indexOf("Connection reset") > -1) {
				this.close();
			}
			throw new RuntimeException(e);
		} finally {
			try {
				rs.close();
			} catch (Exception e) {
			}
			try {
				ps.close();
			} catch (Exception e) {
			}
		}
		return list;
	}
	/**
	 * 获得查询语句的总记录数
	 */
	@SuppressWarnings("unused")
	private int countSql(String sql, Object... objs) {
		String sql1 = sql;
		String upSql = sql.toUpperCase();
		int order = upSql.lastIndexOf(" ORDER ");
		if (order > sql.lastIndexOf(")")) {
			sql1 = sql1.substring(0, order);
		}
		return getSqlCount("select count(*) count0 from (" + sql1 + ") tb", objs);
	}
	/**
	 * 获得sql的统计结果
	 */
	public int getSqlCount(String sql, Object... objs) {
		Map<String, Object> map = queryUnique(sql, objs);
		if (map != null) {
			for (String key : map.keySet()) {
				return Integer.parseInt(map.get(key) + "");
			}
		}
		return 0;
	}

	public Map<String, Object> queryUnique(String sql, Object... objs) {
		List<Map<String, Object>> list = query(sql, objs);
		if (list != null && !list.isEmpty()) {
			return list.get(0);
		}
		return null;
	}
	/**
	 * 得到表字段属性
	 * 包括：
	 * name：列名
	 * isNotNull：字段是否能为空
	 * autoAdd：是否自动增长
	 * length：字段长度
	 * text：字段说明
	 * type：字段在数据库的类型名称
	 * signed：
	 * readOnly：只读
	 */
	public List<Map<String, Object>> getFields(String table) {
		List list = new ArrayList();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			String sql = "select * from " + table + " where 1=2";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			ps.getMetaData();
			Map<String, Object> map = null;
			ResultSetMetaData rsmd = ps.getMetaData();
			int size = rsmd.getColumnCount();
			String name = null;
			
			for (int i = 1; i <= size; i++) {
				try {
					map = new HashMap<String, Object>();
					map.put("name", rsmd.getColumnName(i));
					name = rsmd.getSchemaName(i);
					map.put("text", name == null || "".equals(name) ? rsmd.getColumnLabel(i) : name);
					map.put("type", rsmd.getColumnTypeName(i));
					map.put("length", rsmd.getColumnDisplaySize(i));
					map.put("isNotNull", rsmd.isNullable(i));
					map.put("fixed", rsmd.getScale(i));//保留小数点位数
					map.put("autoAdd", rsmd.isAutoIncrement(i));
					map.put("readOnly", rsmd.isReadOnly(i));
					map.put("signed", rsmd.isSigned(i));
					list.add(map);
				} catch (Exception e) {
				}
			}
		} catch (Exception e) {
			if (e.getMessage().indexOf("Connection reset") > -1) {
				this.close();
			}
			throw new RuntimeException(e);
		} finally {
			try {
				rs.close();
			} catch (Exception e) {
			}
			try {
				ps.close();
			} catch (Exception e) {
			}
		}
		return list;
	}
	
	/**
	 * 执行SQL语句
	 * @param sql
	 * @param objs
	 * @return
	 */
	@SuppressWarnings("null")
	public int execute(String sql, Object... objs) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			if (objs != null && objs.length > 0) {
				for (int i = 0;i < objs.length;i ++) {
					ps.setObject(i+1, objs[i]);
				}
			}
			return ps.executeUpdate();
		} catch (Exception e) {
			if (e.getMessage().indexOf("Connection reset") > -1) {
				this.close();
			}
			throw new RuntimeException(e);
		} finally {
			try {
				rs.close();
			} catch (Exception e) {
			}
			try {
				ps.close();
			} catch (Exception e) {
			}
		}
	}
	
	public void beginTranscation() {
		try {
			if (conn != null && conn.getAutoCommit()) {
				conn.setAutoCommit(false);
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	public void commit() {
		try {
			if (conn != null) {
				conn.commit();
				conn.setAutoCommit(true);
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	public void rollback() {
		try {
			if (conn != null) {
				conn.rollback();
				conn.setAutoCommit(true);
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
	
	public void close() {
		try {
			if (conn != null) {
				ConnectionPool.resetConnection(conn);
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}
}